import os
import xlwings as xw
import pandas as pd
app = xw.App(visible=True, add_book=False)
if not(os.path.exists('参数表\\参数汇总.xlsx')):
    workbook = app.books.add()
    workbook.save(f'参数表\\参数汇总.xlsx')
    workbook.close()
# else:
#     workbook = app.books.open(f'参数表\\参数汇总.xlsx')

    # workbook.close()
file_path = '参数表'
file_list = os.listdir(file_path)


for i in file_list:
    print(i)
    if os.path.splitext(i)[1] in ['.xls', '.xlsx'] and \
            not(os.path.splitext(i)[0].startswith('~$')) and \
            not (i in ['参数汇总.xlsx']):
        workbook1 = app.books.open(file_path + '\\' + i)
        worksheet1 = workbook1.sheets[0]
        # values = worksheet1.range('A2').expand('table').options(
        #     pd.DataFrame, header=1, index=True).value
        values = worksheet1.range('A2').expand('table').options(ndim=2).value
        workbook1.close()
        workbook = app.books.open(f'参数表\\参数汇总.xlsx')
        sheet_names = [j.name for j in workbook.sheets]
        if not(os.path.splitext(i)[0] in sheet_names):
            workbook.sheets.add(os.path.splitext(i)[0])
            workbook.save()
        sheet = workbook.sheets[os.path.splitext(i)[0]]
        sheet['a1:a{}'.format(len(values))].number_format = '@'
        sheet.range('A1').value = values
        sheet.range('A1').expand('table').autofit()
        
workbook.save(f'参数表\\参数汇总.xlsx')
workbook.close()
app.quit()
